import pymysql


class DAO_major_catalog:
    def __init__(self):
        pass

    # 往表 sch_major_catalog 中添加数据，其中年份全为2018
    def add(self, maj_code, maj_name, maj_college_name, maj_uni_name):
        # 打开数据库连接
        db = self.get_conn()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 插入语句
        sql = "INSERT INTO sch_major_catalog VALUES (null, '2020', '%s', '%s', '%s', '%s', '学硕', 3, 0," \
              " null, null, null, null, null)" % \
              (maj_code, maj_name, maj_college_name, maj_uni_name)
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 执行sql语句
            db.commit()
            print("insert ok")
        except:
            # 发生错误时回滚
            print("(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((")
            db.rollback()
        # 关闭数据库连接
        db.close()

    # 更新专业目标表的学术类型属性（这个模块创建于2018-10-23）
    def update_maj_academic(self, maj_academic, maj_uni_name, maj_college_name, maj_name):
        # 打开数据库连接
        db = self.get_conn()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 更新语句
        sql = "UPDATE sch_major_catalog SET maj_academic = '%s' WHERE maj_uni_name = '%s' and maj_college_name = '%s' and maj_name = '%s'" % (maj_academic, maj_uni_name, maj_college_name, maj_name)
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 执行sql语句
            db.commit()
            print(maj_uni_name, maj_college_name, maj_name, "update ok")
        except:
            # 发生错误时回滚
            print("MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM")
            db.rollback()
        # 关闭数据库连接
        db.close()

    # 根据 maj_uni_name, maj_name 判断 maj_academic 的 值是否为空，为空返回True, 非空返回False
    def maj_academic_empty(self, maj_uni_name, maj_name):
        # 打开数据库连接
        db = self.get_conn()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 更新语句，学术类型为空的数量
        sql = "select maj_academic from sch_major_catalog WHERE maj_uni_name = '%s' and maj_name = '%s' and maj_academic = ''" % (maj_uni_name, maj_name)
        # 执行sql语句
        rows = cursor.execute(sql)
        # 执行sql语句
        if rows > 0:
            return True
        else:
            return False


    # 根据 maj_uni_name 判断 maj_academic 的 值是否为空，为空返回True, 非空返回False
    def academic_empty(self, maj_uni_name):
        # 打开数据库连接
        db = self.get_conn()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 语句，所有的专业
        sql = "select maj_academic from sch_major_catalog WHERE maj_uni_name = '%s'" % (maj_uni_name)
        # 执行sql语句
        rows = cursor.execute(sql)
        # SQL语句，学术类型 为空的语句
        empty_sql = "select maj_academic from sch_major_catalog WHERE maj_academic = '' and maj_uni_name = '%s'" % (maj_uni_name)
        empty_rows = cursor.execute(empty_sql)
        # 执行sql语句
        print("为空的：", empty_rows, "所有的：", rows)
        if rows == rows - empty_rows:
            return False
        else:
            return True

    # 与数据库建立连接
    def get_conn(self):

        # 里皮的阿里云服务器
        # conn = pymysql.connect(host='39.105.39.215', port=3307, user="root", passwd="qq1040256886", db="yanxin", charset="utf8")

        # 本机的数据库
        #conn = pymysql.connect(host='127.0.0.1', port=3306, user="root", passwd="root", db="yanxin", charset="utf8")
        conn = pymysql.connect(host='127.0.0.1', port=3306, user="root", passwd="123", db="kaoyanpy", charset="utf8")
        return conn

    # 查询并输出所有数据
    def sel_all(self):
        cursor = self.get_conn().cursor()
        sql = 'select * from sch_major_catalog'
        rows = cursor.execute(sql)
        res = cursor.fetchall()
        print('共有', rows, '条数据')
        for re in res:
            print(re)
        print('共有', rows, '条数据')

    # 查询表是否为空，为空返回true，不为空返回false，
    # 用于truncate()
    def is_empty(self):
        cursor = self.get_conn().cursor()
        sql='select * from sch_major_catalog'
        rows = cursor.execute(sql)
        if rows > 0:
            return False
        else:
            return True

    # 清空 sch_major_catalog 表
    def truncate(self):
        if self.is_empty():
            print("数据为空")
        else:
            sql = "truncate sch_major_catalog"
            conn = self.get_conn()
            conn.cursor().execute(sql)
            conn.commit()
            print("已清空数据")
